import datetime
import queue
import re
import time

import pymysql as pymysql
from dateutil.relativedelta import relativedelta
from fake_useragent import UserAgent
from lxml import etree
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By

import spiderConfig


class basketballFuturel():
    chrome_path = r'./chromedriver.exe'

    def __init__(self, url):
        chrome_options = Options()
        chrome_options.add_argument('--headless')
        chrome_options.add_argument('--disable-gpu')
        self.q = queue.Queue()
        self.driver = webdriver.Chrome(chrome_options=chrome_options)
        self.base_url = url
        self.__host = spiderConfig.host
        self.__port = spiderConfig.port
        self.__user = spiderConfig.user
        self.__password = spiderConfig.password
        self.__db = spiderConfig.db
        self.__charset = spiderConfig.charset
        self.lists = []
        self.dicts = {}
        self.s = ""
        self.ua = UserAgent()

    def sql_operate(self, lists):
        sql__conn = pymysql.connect(
            host=self.__host,
            port=self.__port,
            user=self.__user,
            password=self.__password,
            db=self.__db,
            charset=self.__charset
        )
        cursor = sql__conn.cursor()

        # 先delete tp_game_next 表
        start_dates, end_dates = self.create_yesterday()
        sql_delete = "DELETE FROM tp_game where  tag = 2 and  create_time BETWEEN '{}' AND '{}'".format(
            start_dates, end_dates)
        print(sql_delete)
        cursor.execute(sql_delete)
        sql__conn.commit()

        cursor = sql__conn.cursor()
        for single in lists:
            sql_inert = "insert into tp_game values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            data = [(single['id'], single['game_type'], single['match_name'], single['match_date'], single['home_team'],
                     single['away_team'], single['half_score'], single['final_score'], single['match_status'],
                     single['minutes'],
                     single['fx_id'], single['hc'], single['hc_home'], single['hc_away'], single['ou'], single['ou_o'],
                     single['ou_u'],
                     single['hc_half'], single['hc_home_half'], single['hc_away_half'], single['ou_half'],
                     single['ou_o_half'],
                     single['ou_u_half'], single['style'], single['sort'], single['create_time'], single['update_time'],
                     single['tag'])]
            print(data)
            print('正在插入中')
            cursor.executemany(sql_inert, data)
            sql__conn.commit()
        cursor.close()
        sql__conn.close()

    def get_html(self, page_index):
        global is_choice_window
        try:
            if '@id="nav2"' in page_index:
                self.driver.get(self.base_url)
                self.driver.implicitly_wait(10)
            self.driver.find_element(By.XPATH, '{}'.format(page_index)).click()
        except Exception:
            self.driver.quit()
            time.sleep(1)
            self.main()
        is_choice_window = self.driver.window_handles
        if len(is_choice_window) == 2:
            self.driver.close()
        self.driver.switch_to.window(self.driver.window_handles[0])
        self.driver.implicitly_wait(10)
        complete_data = self.driver.page_source
        return complete_data

    # 解析出详情页url
    def parse_detail_url(self, complete_data):
        complete_html = etree.HTML(complete_data)
        total_tables = complete_html.xpath('//table[@class="score-table"]')
        return total_tables

    def assemble_data(self, total_tbody):
        self.lists = []
        for tbody in total_tbody:
            is_hide = "".join(tbody.xpath('@style'))
            if is_hide != 'display:none':
                is_td_leng = len(tbody.xpath('tbody/tr[2]/td'))
                tr_id = "".join(tbody.xpath('@id'))
                match_name = "".join(tbody.xpath('tbody/tr[2]/td[2]/span/a/text()'))
                if not match_name:
                    match_name = "".join(tbody.xpath('tbody/tr[2]/td[2]/span/text()'))
                match_date = "".join(tbody.xpath('tbody/tr[2]/td[3]/text()'))
                match_date = self.get_time_stamp(match_date)
                home_team_1 = "".join(tbody.xpath('tbody/tr[2]/td[4]/a/text()')).split('[')[0]
                home_team_2 = "".join(tbody.xpath('tbody/tr[2]/td[4]/span/span/text()'))
                home_team = home_team_1 + home_team_2
                away_team_1 = "".join(tbody.xpath('tbody/tr[3]/td[1]/a/text()')).split('[')[0]
                away_team_2 = "".join(tbody.xpath('tbody/tr[3]/td[1]/span/span/text()'))
                away_team = away_team_1 + away_team_2
                match_status = "".join(tbody.xpath('tbody/tr[1]/td[2]/span/span[1]/text()'))
                minutes_1 = "".join(tbody.xpath('tbody/tr[1]/td[2]/span[2]/span[1]/text()'))
                minutes_2 = "".join(tbody.xpath('tbody/tr[1]/td[2]/span[2]/span[2]/text()'))
                minutes = minutes_1 + minutes_2.strip()
                if not minutes:
                    minutes = "".join(tbody.xpath('tbody/tr[1]/td[2]/span[2]/span/b/text()')).strip()
                home_half_score_1 = "".join(tbody.xpath('tbody/tr[2]/td[9]/font[1]/text()'))
                home_half_score_2 = "".join(tbody.xpath('tbody/tr[2]/td[9]/text()'))
                home_half_score_3 = "".join(tbody.xpath('tbody/tr[2]/td[9]/font[2]/text()'))
                home_half_score = home_half_score_1 + home_half_score_2 + home_half_score_3
                away_half_score_1 = "".join(tbody.xpath('tbody/tr[3]/td[6]/font[1]/text()'))
                away_half_score_2 = "".join(tbody.xpath('tbody/tr[3]/td[6]/text()'))
                away_half_score_3 = "".join(tbody.xpath('tbody/tr[3]/td[6]/font[2]/text()'))
                if away_half_score_1 and away_half_score_3:
                    away_half_score = "-" + away_half_score_1 + away_half_score_2 + away_half_score_3
                    half_score = home_half_score + "," + away_half_score
                else:
                    half_score = ""
                home_final_score = "".join(tbody.xpath('tbody/tr[2]/td[10]/text()'))
                away_final_score = "".join(tbody.xpath('tbody/tr[3]/td[7]/text()'))
                if home_final_score and away_final_score:
                    final_score = home_final_score + ',' + '-' + away_final_score
                else:
                    final_score = ""
                hc = "".join(tbody.xpath('tbody/tr[2]/td[14]/a/span[1]/text()'))
                if not hc:
                    hc = "".join(tbody.xpath('tbody/tr[3]/td[12]/a/span[1]/text()'))
                if hc:
                    "-" + "".join(tbody.xpath('tbody/tr[3]/td[12]/a/span[1]/text()'))
                hc_home = "".join(tbody.xpath('tbody/tr[2]/td[14]/a/span[2]/text()'))
                hc_away = "".join(tbody.xpath('tbody/tr[3]/td[11]/a/span[2]/text()'))
                ou = "".join(tbody.xpath('tbody/tr[2]/td[15]/a/text()'))
                ou = "".join(ou).replace(" ", '').replace("大", "").replace("小", "").strip()
                ou = ou.split('/')[0]
                if not ou:
                    ou = 0
                ou_o = "".join(tbody.xpath('tbody/tr[2]/td[15]/a/span/text()'))
                ou_u = "".join(tbody.xpath('tbody/tr[3]/td[12]/a/span/text()'))
                if is_td_leng != 16:
                    home_half_score_1 = "".join(tbody.xpath('/tbody/tr[2]/td[10]/font[1]/text()'))
                    home_half_score_2 = "".join(tbody.xpath('/tbody/tr[2]/td[10]/text()'))
                    home_half_score_3 = "".join(tbody.xpath('/tbody/tr[2]/td[10]/font[2]/text()'))
                    home_half_score = home_half_score_1 + home_half_score_2 + home_half_score_3
                    away_half_score_1 = "".join(tbody.xpath('/tbody/tr[3]/td[7]/font[1]/text()'))
                    away_half_score_2 = "".join(tbody.xpath('/tbody/tr[3]/td[7]/text()'))
                    away_half_score_3 = "".join(tbody.xpath('/tbody/tr[3]/td[7]/font[2]/text()'))
                    away_half_score = "-" + away_half_score_1 + away_half_score_2 + away_half_score_3
                    half_score = home_half_score + "," + away_half_score
                    home_final_score = "".join(tbody.xpath('/tbody/tr[2]/td[11]/text()'))
                    away_final_score = "".join(tbody.xpath('/tbody/tr[3]/td[8]/text()'))
                    if home_final_score and away_final_score:
                        final_score = home_final_score + ',' + '-' + away_final_score
                    else:
                        final_score = ""
                    hc = "".join(tbody.xpath('/tbody/tr[2]/td[15]/a/span[1]/text()'))
                    if not hc:
                        hc = "".join(tbody.xpath('/tbody/tr[3]/td[12]/a/span[1]/text()'))
                        if hc:
                            "-" + "".join(tbody.xpath('/tbody/tr[3]/td[12]/a/span[1]/text()'))
                    hc_home = "".join(tbody.xpath('/tbody/tr[2]/td[15]/a/span[2]/text()'))
                    hc_away = "".join(tbody.xpath('/tbody/tr[3]/td[12]/a/span[2]/text()'))
                    ou = "".join(tbody.xpath('/tbody/tr[2]/td[16]/a/text()'))
                    ou = "".join(ou).replace(" ", '').replace("大", "").replace("小", "").strip()
                    ou = ou.split('/')[0]
                    if not ou:
                        ou = 0
                    ou_o = "".join(tbody.xpath('/tbody/tr[2]/td[16]/a/span/text()'))
                    ou_u = "".join(tbody.xpath('/tbody/tr[3]/td[13]/a/span/text()'))

                # 点入亚按钮
                is_hc_half_path = '//*[@id="{}"]/tbody/tr/td[16]/a[2]'.format(tr_id)
                if not is_hc_half_path:
                    is_hc_half_path = '//*[@id="{}"]/tbody/tr/td[16]/a[2]'.format(tr_id)
                is_ou_half_path = '//*[@id="{}"]/tbody/tr/td[13]/a[1]'.format(tr_id)
                if not is_ou_half_path:
                    is_ou_half_path = '//*[@id="{}"]/tbody/tr/td[14]/a[1]'.format(tr_id)
                is_hc_xpath = 1
                hc_home_half, hc_half, hc_away_half = self.get_selenium_contorl(is_hc_xpath, is_hc_half_path)
                is_ou_xpath = 2
                ou_o_half, ou_half, ou_u_half = self.get_selenium_contorl(is_ou_xpath, is_ou_half_path)
                if not ou_half:
                    ou_half = 0
                match_name = "".join(tbody.xpath('/tbody/tr[2]/td[2]/span/a/text()'))
                if not match_name:
                    match_name = "".join(tbody.xpath('/tbody/tr[2]/td[2]/span/text()'))
                if not match_status:
                    match_status = 0
                times = str(time.time())[:10]
                self.dicts = {
                    "id": 0,
                    "game_type": 2,
                    "match_name": match_name,
                    "match_date": match_date,
                    "home_team": home_team_1,
                    "away_team": away_team_1,
                    "half_score": half_score,
                    "final_score": final_score,
                    "match_status": match_status,
                    "minutes": minutes,
                    "fx_id": 0,
                    "hc": hc,
                    "hc_home": hc_home,
                    "hc_away": hc_away,
                    "ou": ou,
                    "ou_o": ou_o,
                    "ou_u": ou_u,
                    "hc_half": hc_half,
                    "hc_home_half": hc_home_half,
                    "hc_away_half": hc_away_half,
                    "ou_half": ou_half,
                    "ou_o_half": ou_o_half,
                    "ou_u_half": ou_u_half,
                    "style": 0,
                    "sort": 1,
                    "create_time": times,
                    "update_time": times,
                    "tag": 2
                }
                print(self.dicts)
                self.lists.append(self.dicts)
        return self.lists

    def get_selenium_contorl(self, is_xpath, is_hc_half_path):
        global is_window
        global is_win_xpath
        ret1 = ''
        ret2 = ''
        ret3 = ''
        try:
            self.driver.find_element(By.XPATH, is_hc_half_path).click()
            windows_all = self.driver.window_handles
            if len(windows_all) == 2:
                is_window = 2
                time.sleep(1)
                self.driver.implicitly_wait(10)
                self.driver.switch_to.window(self.driver.window_handles[1])
                self.driver.find_element(By.XPATH, '//*[@id="tabs"]/a[2]').click()
                self.driver.implicitly_wait(10)
                complete_data = self.driver.page_source
                pattern = re.compile(
                    '''36\*.*?</td>.*?<td.*?oddstype="wholeLastOdds">.*?">(.*?)</font>.*?<td id=".*?" oddstype="wholeLastOdds">(.*?)</td>.*?<font color=".*?">(.*?)</font>''',
                    re.S)
                rets = re.findall(pattern, complete_data)
                if rets:
                    ret1 = str(rets[0][0]).strip()
                    if ret1 == '&nbsp;':
                        ret1 = ''
                    ret2 = str(rets[0][1]).strip()
                    if ret2 == '&nbsp;':
                        ret2 = ''
                    ret3 = str(rets[0][2]).strip()
                    if ret3 == '&nbsp;':
                        ret3 = ''
                self.driver.close()
                self.driver.switch_to.window(self.driver.window_handles[0])
                return ret1, ret2, ret3
        except Exception:
            print("操作太频繁了，请先歇一歇。")
            time.sleep(1)
            if is_window == 2:
                self.driver.close()
                self.driver.switch_to.window(self.driver.window_handles[0])
            return ret1, ret2, ret3

    def get_time_stamp(self, match_date):
        curr_day = datetime.datetime.now()
        curr_year = str(curr_day.year)
        match_day = match_date.replace("月", "-").replace("日", " ")
        match_days = "{}-{}".format(curr_year, match_day)
        match_date = time.strptime(match_days, "%Y-%m-%d %H:%M")
        match_date = int(time.mktime(match_date))
        return match_date

    def create_yesterday(self):
        curr_day = datetime.datetime.now()
        curr_hour = str(curr_day.hour)
        current_day = datetime.date.today()
        yesterday = str(current_day - relativedelta(days=1))
        start_dates_time = "1970-1-20" + " " + "00:00:00"
        end_dates_time = yesterday + " " + "23:59:00"
        start_dates_time = time.strptime(start_dates_time, "%Y-%m-%d %H:%M:%S")
        end_dates_time = time.strptime(end_dates_time, "%Y-%m-%d %H:%M:%S")
        start_dates = int(time.mktime(start_dates_time))
        print(start_dates)
        end_dates = int(time.mktime(end_dates_time))
        return start_dates, end_dates

    def main(self):
        html_page_url = ['//*[@id="nav2"]/ul[1]/li[2]/a', '//*[@id="dateList"]/a[2]', '//*[@id="dateList"]/a[3]']
        for page_xpath in html_page_url:
            complete_data = self.get_html(page_xpath)
            total_tables = self.parse_detail_url(complete_data)
            lists = self.assemble_data(total_tables)
            self.sql_operate(lists)
        self.driver.quit()


if __name__ == '__main__':
    run = basketballFuturel('http://lq3.titan007.com/nba.htm')
    run.main()
